﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_LABEL_Get_Search_Count')
	BEGIN
		DROP Procedure usp_UPDMS_COM_LABEL_Get_Search_Count
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_LABEL_Get_Search_Count
**	Desc : 라벨관리의 검색결과수를 카운트한다.
**	Test Exec Query : Exec usp_UPDMS_COM_LABEL_Get_Search_Count 'ㄱ','','',''
**	Called by : Com_Dac_UPDMS_COM_LABEL.cs
**	Program ID : Com1017m
**	Auth : 송시명
**	Date : 2011-01-12
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_LABEL_Get_Search_Count]
@ls_initial nvarchar(1),
@ls_label_ko nvarchar(200),
@ls_label_en nvarchar(400),
@ls_label_ch nvarchar(400)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT COUNT(Seq) Search_Count
  FROM (
       SELECT Seq,
              Label_Ko,
              Label_En,
              Label_Ch,
              CASE WHEN [Label_Ko] < 'ㄱ' THEN SUBSTRING([Label_Ko], 1, 1)
                   WHEN [Label_Ko] < '나' THEN 'ㄱ'
                   WHEN [Label_Ko] < '다' THEN 'ㄴ'
                   WHEN [Label_Ko] < '라' THEN 'ㄷ'
                   WHEN [Label_Ko] < '마' THEN 'ㄹ'
                   WHEN [Label_Ko] < '바' THEN 'ㅁ'
                   WHEN [Label_Ko] < '사' THEN 'ㅂ'
                   WHEN [Label_Ko] < '아' THEN 'ㅅ'
                   WHEN [Label_Ko] < '자' THEN 'ㅇ'
                   WHEN [Label_Ko] < '차' THEN 'ㅈ'
                   WHEN [Label_Ko] < '카' THEN 'ㅊ'
                   WHEN [Label_Ko] < '타' THEN 'ㅋ'
                   WHEN [Label_Ko] < '파' THEN 'ㅌ'
                   WHEN [Label_Ko] < '하' THEN 'ㅍ'
                   WHEN [Label_Ko] < 'a'  THEN 'ㅎ'
                   ELSE SUBSTRING([Label_Ko], 1, 1)
               END Initial
          FROM UPDMS_COM_LABEL WITH(NOLOCK)
	   ) z
 WHERE z.Initial LIKE @ls_initial + '%'
   AND z.Label_Ko LIKE '%' + @ls_label_ko +'%'
   AND ISNULL(z.Label_En,'') LIKE '%' + @ls_label_en + '%'
   AND ISNULL(z.Label_Ch,'') LIKE '%' + @ls_label_ch + '%'

GO